<?php
declare (strict_types = 1);

namespace app\command\logic;

use app\command\logic\BaseLogic;
use think\console\Output;

/**
 * 统计-商家-商品数据
 * php think cli stats_shop_goods --msg='{"days":30}'
 */
class CliStatsShopGoodsLogic extends BaseLogic implements CliInterface
{

    /**
     * 入口方法
     * {@inheritdoc}
     */
    public function run(Output $output, array $msg): bool
    {
        $insertData = [];
        $date       = isset($msg['date']) ? ($msg['date'] == 'today' ? date('Y-m-d') : $msg['date']) : null;
        if ( ! empty($date)) {
            self::dataForGoods($date);
            self::dataForGoodsSpu($date);
        } else {
            $days = intval($msg['days'] ?? 1);
            for ($i = 1; $i <= $days; $i++) {
                $date = date('Y-m-d', strtotime("-$i days"));
                // echo $date . "\n";exit;
                self::dataForGoods($date);
                self::dataForGoodsSpu($date);
            }
        }
        return true;
    }

    /**
     * 统计商品数据
     * @param  [type] $date [description]
     * @return [type]       [description]
     */
    private static function dataForGoods($date)
    {
        $end        = $date . ' 23:59:59';
        $insertData = [];

        $field = [
            'shop_id',
            'COALESCE(sum(CASE WHEN status = 0 THEN 1 ELSE 0 END), 0) AS waiting_goods',
            'COALESCE(sum(CASE WHEN status = 1 THEN 1 ELSE 0 END), 0) AS sale_goods',
            'COALESCE(sum(CASE WHEN status = 2 THEN 1 ELSE 0 END), 0) AS delisted_goods',
        ];
        // use index idx_Status_Deleted_Type_Category_Sales
        $items = \think\facade\Db::table('goods')
            ->field($field)
            ->where('deleted', 0)
        // ->where('status', 1) // 0审核中 1上架 2下架
            ->where('create_at', '<', $end)
            ->group('shop_id')
            ->select()->toArray();
        // echo json_encode($items);exit;
        if (empty($items)) {
            return [];
        }

        // use index idx_create_at_status_deleted
        // '0待支付 1待发货 2待收款 3已收货 4已完成 5退款中 6团款成功
        $field2 = [
            'shop_id',
            'COALESCE(SUM(CASE WHEN status IN (0, 1, 2, 3, 4, 5, 6) THEN quantity ELSE 0 END), 0) AS order_goods', // 下单件数
            'COALESCE(SUM(CASE WHEN status IN (1, 2, 3, 4, 5, 6) THEN quantity ELSE 0 END), 0) AS pay_goods', // 支付件数
            'COALESCE(SUM(CASE WHEN status IN (1, 2, 3, 4, 5, 6) THEN sell_price ELSE 0 END), 0) AS sell_price', //商品金额
        ];
        $items2 = \think\facade\Db::table('order_goods')
            ->field($field2)
            ->where('deleted', 0)
            ->whereBetweenTime('create_at', $date, $end)
            ->group('shop_id')
            ->select()->toArray();
        $map2 = array_column($items2, null, 'shop_id');
        // echo \think\facade\Db::table('order_goods')->getlastsql();
        // var_dump($items);exit;
        foreach ($items as $data) {
            $data2 = $map2[$data['shop_id']] ?? [];

            $data['date']        = $date;
            $data['order_goods'] = $data2['order_goods'] ?? 0;
            $data['pay_goods']   = $data2['pay_goods'] ?? 0;
            $data['sell_price']  = $data2['sell_price'] ?? 0;
            // echo \think\facade\Db::table('goods')->getlastsql();exit;
            // use index idx_Api_CreateAt
            $data['view_count'] = \think\facade\Db::table('member_oplog')
                ->where('api', '/api/goods/goodsDetail')
                ->whereLike('params', '%_shop_' . $data['shop_id'] . '_shop_%')
                ->whereBetweenTime('create_at', $date, $end)
                ->count();
            // echo \think\facade\Db::table('member_oplog')->getlastsql();exit;

            $data['add_cart'] = \think\facade\Db::table('member_cart')
                ->where('deleted', 0)
                ->where('shop_id', $data['shop_id'])
                ->whereBetweenTime('create_at', $date, $end)
                ->sum('quantity');

            // echo \think\facade\Db::table('order_goods')->getlastsql();exit;
            $data['rate'] = $data['pay_goods'] > 0 ? $data['view_count'] / $data['pay_goods'] : 0; //转换率 (浏览量/支付件数)

            $insertData[] = $data;
        }

        if ( ! empty($insertData)) {
            \think\facade\Db::table('stats_shop_goods')
                ->replace()
                ->insertAll($insertData);
        }
        return true;
    }

    /**
     * 统计商品数据排行
     * @param  [type] $date [description]
     * @return [type]       [description]
     */
    private static function dataForGoodsSpu($date)
    {
        $end = $date . ' 23:59:59';

        $field = [
            'shop_id',
            'id as goods_id',
            'goods_title',
        ];
        // use index idx_Status_Deleted_Type_Category_Sales
        $items = \think\facade\Db::table('goods')
            ->field($field)
            ->where('deleted', 0)
            ->whereIn('status', [1, 2]) // 0审核中 1上架 2下架
            ->select()->toArray();
        // echo json_encode($items);exit;
        if (empty($items)) {
            return [];
        }

        $goods_ids = array_column($items, 'goods_id');

        // use index idx_create_at_status_deleted
        // 0待支付 1待发货 2待收货 3已收货 4已完成 5退款中 6退款成功 7取消 8超时取消 9待退货
        $field = [
            'goods_id',
            // 'COALESCE(SUM(CASE WHEN deleted = 0 THEN quantity ELSE 0 END), 0) AS order_goods', //下单件数
            'COALESCE(SUM(CASE WHEN deleted = 0 AND status IN (0, 1, 2, 3, 4, 5, 6) THEN quantity ELSE 0 END), 0) AS order_goods', // 支付件数
            'COALESCE(SUM(CASE WHEN deleted = 0 AND status IN (1, 2, 3, 4, 5, 6) THEN quantity ELSE 0 END), 0) AS pay_goods', // 支付件数
        ];
        $items2 = \think\facade\Db::table('order_goods')
            ->field($field)
            ->whereBetweenTime('create_at', $date, $end)
            ->where('deleted', 0)
            ->whereIn('goods_id', $goods_ids)
            ->group('goods_id')
            ->select()->toArray();
        // echo \think\facade\Db::table('order_goods')->getlastsql();exit;
        $orderMap = array_column($items2, null, 'goods_id');
        // echo json_encode($orderMap, 320);exit;
        $insertDataSpu = [];
        // echo \think\facade\Db::table('order_goods')->getlastsql();
        // var_dump($data);exit;
        foreach ($items as $key => $data) {
            $od                  = $orderMap[$data['goods_id']] ?? [];
            $data['order_goods'] = $od['order_goods'] ?? 0;
            $data['pay_goods']   = $od['pay_goods'] ?? 0;
            // $data['view_count']  = $g['view_count'];

            $data['view_count'] = \think\facade\Db::table('member_oplog')
            // ->whereIn('api', ['/api/goods/goodsDetail', '/api/supply/goodsDetail'])
                ->where('api', '/api/goods/goodsDetail')
                ->where('params', 'like', '%{"goodsId":"' . $data['goods_id'] . '"%')
                ->whereBetweenTime('create_at', $date, $end)
                ->count();
            $data['rate'] = $data['pay_goods'] > 0 ? $data['view_count'] / $data['pay_goods'] : 0; //转换率 (浏览量/支付件数)

            // echo \think\facade\Db::table('member_oplog')->getlastsql();exit;

            $data['add_cart'] = \think\facade\Db::table('member_cart')
                ->where('deleted', 0)
                ->where('goods_id', $data['goods_id'])
                ->whereBetweenTime('create_at', $date, $end)
                ->sum('quantity');

            $data['date'] = $date;

            $insertDataSpu[] = $data;
        }

        \think\facade\Db::table('stats_shop_goods_spu')
            ->replace()
            ->insertAll($insertDataSpu);

        return $data;
    }
}
